/*
 * Class Customer_DTO
 * 
 * Copyright by Nguyen Phi Hung
 */
package DAL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * This class used for description information of a customer
 * 
 * @version 1.0.2 24 Apr 2013
 * @author Nguyen Phi Hung
 */
public class SQLConnect { 
    /***/
    private String username;
    /***/
    private String password;
    /***/
    private String Host;
    /***/
    private String Database;
    /***/
    private Connection connect = null;
    /***/
    private Statement statement = null;
    /***/
    private ResultSet result = null;


    public SQLConnect(String Host,
            String UserName,
            String Password,
            String DataBase){
        this.Host = Host;
        this.Database = DataBase;
        this.password = Password;
        this.username = UserName;
    }
    /**
     * Check for SQL server JDBC driver
     * @throws Exception 
     */
    private void testDriver() throws Exception {
        try {
            //Check for Class Name
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (java.lang.ClassNotFoundException e) {
            //Throw an exception
            throw new Exception("SQL Server JDBC Driver not found.");
        }
    }
    
    /***
     * Connect to database
     * @return Connection
     * @throws Exception 
     */
    public Connection getConnect() throws Exception {
        if(connect==null) {
            //Check for driver
            testDriver();            
            
//            url = "jdbc:sqlserver://"+ this.Host + ":1433;databaseName=" + Database;
            String url = "jdbc:sqlserver://localhost:1433;databaseName="+this.Database;
            try {
                //Try to connect to database
                this.connect = DriverManager.getConnection(url, username, password);
            } catch (java.sql.SQLException e) {
                throw new Exception("Cannot connect to database");
            }
        }
        //Return a Connection
        return this.connect;
    }
    /***
     * Create new Statement if it is null or closed
     * @return Statement
     * @throws Exception 
     */
    private Statement getStatement()throws Exception {
        //Check for statement status
        if(this.statement==null?true:this.statement.isClosed()) {
            this.statement = getConnect().createStatement();
        }
        //Return a statement
        return this.statement;
    }
    
    /***
     * Close Statement and Connection
     * @throws Exception 
     */
    protected void disconnect() throws Exception {     
        //Close Resultset
        if((this.result != null) && !this.result.isClosed()) {
            this.result.close();
            this.result = null;
        }     
        //Close Statement
        if((this.statement != null) && !this.statement.isClosed()) {
            this.statement.close();
            this.statement = null;
        }        
        //Close Connection
        if((this.connect != null) && !this.connect.isClosed()) {
            this.connect.close();
            this.connect = null;
        }
    }
    
    /***
     * Execute sqlQuery and get its result
     * @param sqlQuery
     * @return ResultSet
     * @throws Exception 
     */
    public ResultSet executeQuery(String sqlQuery)throws Exception {     
        try {
            //Execute Query
            this.result = getStatement().executeQuery(sqlQuery);
        } catch (Exception e) {
            throw new Exception("ERROR: " + e.getMessage() + " - " + sqlQuery);
        }
        return this.result;
    }
    
    /***
     * Execute sqlNonQuery and get its result then close Connection
     * Use for Insert, Update, Delete statement
     * @param sqlQuery
     * @return integer
     * @throws Exception 
     */
    public int executeUpdate(String sqlQuery) throws Exception {
        int res = Integer.MIN_VALUE;
        try {
            //Execute Update
            res = getStatement().executeUpdate(sqlQuery);            
        } catch (Exception e) {
            throw new Exception("ERROR: " + e.getMessage() + " - " + sqlQuery);
        } finally {
            //Close Connection
            this.disconnect();
        }
        return res;
    }
}
